Example of using a Criteria in Query
1) =, >, <, >= , <= , <> (not equal) comparison operator1
These operators can be used for number, currency, date and text of data type field. The meaning is the same meaning for mathematic operator.
2) Using “Is null”
It is used as a criteria to get the empty data of that field. For example, you want to get a list of task that has not completed or finished. You can put the “Is null” in the criteria on FinishDate field.
3) Using “Is not null”
Is Not Null operator works in opposite direction from the “Is Null” operator. We can use to get a list of task completed by put “Is Not Null” on the criteria of FinishDate field as shown in the picture below:
4) Using Date() and Date()-1
Date() is used in criteria to get today date like put in the DueDate field to see if any task due today (4/28/14)
Date()-1 is today date -1 day is yesterday. If today is 4/28/14 then Date() -1 is 4/27/14.
5) Use # for Date data type
We use the pound sign (#) in the front and end of date to get the value from the date data type field. Example below is a criteria to get the list of task start from 3/1/14 until now.
6) Create new field in query
A new field can be created with a new name followed by colon( : ) and a reference a format to a main field name. for instance, creating a new field name YearOfStart from StartDate field with format as year 2014 and a Project Name as copy of field TaskName.
7) Format month as 1, Jan, January
The previous example shows the year format as 4 digit of year number (2014). The format of month is similar method.
Example of month format:
M = 1,2,3,..3
Mm = 01,02,03,….
Mmm = Jan, Feb, Mar, …
Mmmm = January, February, March, …
Sometime we want to sort the month from January to December as 1-12, we can use the format of “mm” to get a number of 01 – 12 and sort it Ascending or Descending as shown on the above example.
8) Format Day “ddd” for Mon, Tue, Wed, … or “dddd” for Monday, Tuesday, Wednesday,…
9) Format Date as “d” = 1,8,15,27, or “dd” = 01,08,15,27
10) Enter parameter
We use a bracket […] and put a message inside the bracket for instruction what to do.
We put [Enter a year ex ‘2013’] to tell use to enter a year in the YearStart field that has a format of 4 digit year (2013).
We can use two separate parameters on two separate fields in criteria. For instance, we want to get list of task that started and finished from the specific date and we want the users to enter their own date. Below is an example of using two parameter on date fields.
a. Enter two parameters on StartDate and FinishDate field
b. Enter a year number with a 4-digit number format as ‘2013’
c. Get the result
11) “Like” operator
Like is mostly used in the text data field and follow by question mark (?) and the asterisk or star (*) in the front or back or both front and back of the keyword. The question mark (?) represents a single character only.
Like can also be used in the parameter entry as example below:
a. Enter like with parameter on the criteria
b. Enter “m” in the parameter
But one should be aware that many online pharmacies sell cialis discount at an economical price. Depression is viagra tablet for sale also one of the most popular psychological causes of impotency. It also comes as kamagra oral jelly 100mg. viagra online However, generic viagra online http://greyandgrey.com/wp-content/uploads/2018/07/Terranova.pdf there are many elders, who are quite busy in making sales.
c. Get result of customer list with name starts with “M” only
12 ) “AND” operator
AND operator can be used for text, number and date field. The result of AND will be narrowed down to match the parameter in the criteria. AND can be used inside one field or on two or more fields. AND can be used by entering more than one criteria in the same line, but different field name as show below:
13) “OR” operator
OR operator can be used for text, number and date field. The result of OR will be combined from the result of either match the 1st criteria or the 2nd criteria. It can be used inside one field or on two or more fields. OR can be used by entering more than one criteria in different field in one query, but must entered in a different line for each criteria as show below:
14) Between…..And
The Between…And can be used in the text, number and date field. The syntax of operator is like >= x <=
Criteria Entry |
Result |
| Between 1 and 100 | Display a list that has number from 1 to 100 |
| Between #1/1/14# and #12/31/14# | Display a list that has a date start from1/1/14 to 12/31/14 or whole year of 2014 |
| Between “M*” And “T*” | Display a list of name that start with letter M to letter T |
It can be used with the parameter entry from the user as shown on the example below:
15) Link to field on the opened form
The criteria of field in the query can be linked to that field in the form. The form that linked must be opened in order to have the query works. For instance, we need a query to show a record of the customer that shows in the Customer form, the criteria in the Customer_id will be “[Forms]![Customer]![Customer_id].” The query links the customer record to Customer form by Customer_id as screen shot below:
16) Rename field name: A field name can changed in the query. The format of changing new name is a new name followed by colon (:) and the old field name as an example below of changing a field name CustomerName to Customer.
